package org.alex.samples.model;

import java.io.InputStream;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils  {

	
	public static final String OFFICE_EXCEL_2003_POSTFIX=".xls";
	public static final String OFFICE_EXCEL_2007_POSTFIX=".xlsx";
	

	public static Workbook openWorkbook(InputStream is, String postfix) {
		Workbook hssfWorkbook = null;
		try{
		if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
			hssfWorkbook = new HSSFWorkbook(is);
		}else if(OFFICE_EXCEL_2007_POSTFIX.equals(postfix)){
			hssfWorkbook = new XSSFWorkbook(is);
		}
		}catch(Exception e){
			if(e instanceof NotOLE2FileException){
				return null;
			}
			e.printStackTrace();
		}
		// TODO Auto-generated method stub
		return hssfWorkbook;
	}

	public static List<User> getStudentListByExcel(Workbook wk) {
		final String GENGER_MALE_STRING = "Male",GENGER_FEMALE_STRING = "Female";
		final Integer GENGER_MALE_INTEGER = 0,GENGER_FEMALE_INTEGER = 1;
		List<User> rsultList = new ArrayList<User>();
		Sheet sheet = wk.getSheetAt(0);
		int maxCellNumber = 0;
		int maxRowNumber = sheet.getLastRowNum();
		User vo = null;
		for(int currentRowNumber = 1;currentRowNumber <= maxRowNumber;currentRowNumber ++){
			Row row = sheet.getRow(currentRowNumber);
			maxCellNumber = row.getLastCellNum();
			vo = new User();
			for(int currentCellNumber = 0;currentCellNumber < maxCellNumber; currentCellNumber++){
				Cell cell = row.getCell(currentCellNumber);
				switch(currentCellNumber){
				case 0:
					vo.setId((String) getCellValue(cell));
					break;
				case 1:
					vo.setName((String) getCellValue(cell));
					break;
				case 2:
					String gender = (String) getCellValue(cell);
					if(GENGER_MALE_STRING.equals(gender)){
						vo.setGender(GENGER_MALE_INTEGER);
					}else if(GENGER_FEMALE_STRING.equals(gender)){
						vo.setGender(GENGER_FEMALE_INTEGER);
					}
					break;
				case 3:
					vo.setBirthday((String) getCellValue(cell));
					break;
				case 4:
					vo.setTel((String)getCellValue(cell));
					break;
				case 5:
					vo.setAddress((String)getCellValue(cell));
					break;
				case 6:
					String decodingPassword = (String) getCellValue(cell);
					vo.setPsw(MD5Utils.GetMD5Code(decodingPassword));
					//vo.setPsw(decodingPassword);
					break;
				}
			}
			rsultList.add(vo);
		}
		// TODO Auto-generated method stub
		return rsultList;
	}

	@SuppressWarnings("static-access")
	private static String getCellValue(Cell cell) {
		if(cell == null){
			return "";
		}
		if(cell.getCellType() == cell.CELL_TYPE_BOOLEAN){
			return String.valueOf(cell.getBooleanCellValue());
		}else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){
			DecimalFormat df = new DecimalFormat("0");
			return df.format(cell.getNumericCellValue());
		}else{
			return String.valueOf(cell.getStringCellValue());
		}
		
	}
}




